package com.suoluo.zenapp.dao.dest;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;

import com.suoluo.zenapp.pojo.dest.DestExtPOJO;
import com.suoluo.zenapp.pojo.dest.DestImgResourcePOJO;
import com.suoluo.zenapp.pojo.dest.DestResourceExtPOJO;
import com.suoluo.zenapp.pojo.dest.DestResourcePOJO;
import com.suoluo.zenapp.pojo.dest.DestTextResourcePOJO;
import com.suoluo.zenapp.pojo.dest.DestTrafficPOJO;
import com.suoluo.zenapp.pojo.dest.DestVersionPOJO;
import com.suoluo.zenapp.vo.DestImgResourceVO;
import com.suoluo.zenapp.vo.DestResourceVO;
import com.suoluo.zenapp.vo.DestVO;
import com.suoluo.zenapp.vo.ParameterVO;

@DAO
public interface DestDAO {

	/**
	 * 根据Id查询目的地信息
	 * 
	 * @param startIndex
	 * @param pageCount
	 * @return
	 */
	@SQL("select t1.*,t2.resource_desc destDesc,t3.img_summary imgSummary,t3.orig_img origImg from app_dest_resource t1 "
			+ "join app_dest_text_resource t2 on t1.id = t2.resource_id "
			+ "join app_dest_img_resource t3 on t1.id = t3.attach_resource_id and t3.leaf = 0 where t1.id =:1")
	public DestResourceVO findDestById(int destId);

	/**
	 * 更新目的地信息
	 * 
	 * @param aPPDestResource
	 */
	@SQL("update app_dest_resource set name=:1.name,enname=:1.enname," +
			"lat=:1.lat,lng=:1.lng,rel_id=:1.relId  where id=:1.id")
	public void updateDest(DestResourcePOJO aPPDestResource);

	/**
	 * 更新目的地描述信息
	 * 
	 * @param aPPDestResource
	 */
	@SQL("update app_dest_text_resource set resource_desc=:1.destDesc where resource_id=:1.id")
	public void updateDestDesc(DestResourceVO aPPDestResource);

	/**
	 * 保存目的地资源信息
	 * 
	 * @param dest
	 * @return
	 */
	@ReturnGeneratedKeys
	@SQL("insert into app_dest_resource(name,enname,resource_type,dest_id,"
			+ "parent_id,node_count,leaf_count,layer_index,tree_index,lat,lng,rel_id) "
			+ "values(:1.name,:1.enname,:1.resourceType,:1.destId,:1.parentId,"
			+ ":1.nodeCount,:1.leafCount,:1.layerIndex,:1.treeIndex,:1.lat,:1.lng,:1.relId)")
	public int save(DestResourcePOJO dest);

	/**
	 * 查询目的地资源表所有记录数
	 * 
	 * @return
	 */
	@SQL("select count(id)  from  app_dest_resource where resource_type=1")
	public int findDestResourceCount();

	/**
	 * 查询目的地资源信息
	 * 
	 * @param destName
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	@SQL("select t1.id,t1.name,t1.enname,t1.resource_type resourceType,"
			+ "t1.dest_id destId,t1.parent_id parentId,t1.node_count nodeCount,"
			+ "t1.leaf_count leafCount,t1.layer_index layerIndex,t1.tree_index treeIndex,"
			+ "(select name from app_dest_resource t2  where  t2.id = t1.parent_id) typeName,"
			+ " (select  name from app_dest_resource t3 where  t3.id = t1.dest_id) destName,"
			+ "t4.mini_img miniImg,t1.rel_id relId  "
			+ " from app_dest_resource t1  join app_dest_img_resource t4 on t1.id = t4.attach_resource_id and t4.leaf = 0 "
			+ " where t1.resource_type = 1 "
			+ " order by t1.id desc limit :1.startPage,:1.rows")
	public List<Map> findListDest(ParameterVO para);

	/**
	 * 查询应用版本信息
	 * 
	 * @return
	 */
	@SQL("select * from app_dest_version limit :1.startPage,:1.rows")
	public List<DestVersionPOJO> findDestVersion(ParameterVO param);

	/**
	 * 用目的地Id查询目的地应用的版本信息是否已存在
	 * 
	 * @param destId
	 * @return
	 */
	@SQL("select * from app_dest_version where destId=:1")
	public DestVersionPOJO findVersionByDestId(int destId);

	/**
	 * 保存目的地应用版本信息
	 * 
	 * @param pojo
	 */
	@SQL("insert into app_dest_version(version,destId,app_name,token) values(:1.version,:1.destId,:1.appName,:1.token)")
	public void saveVersion(DestVersionPOJO pojo);

	/**
	 * 根据版本id查询版本信息
	 * 
	 * @param versionId
	 * @return
	 */
	@SQL("select * from app_dest_version where version_id=:1")
	public DestVersionPOJO findVersionById(int versionId);

	/**
	 * 更新版本信息
	 * 
	 * @param version
	 */
	@SQL("update app_dest_version set app_name=:1.appName,version=:1.version where version_id=:1.versionId")
	public void updateVersion(DestVersionPOJO version);

	/**
	 * 查询版本信息的总记录数
	 */
	@SQL("select count(version_id) from app_dest_version")
	public int findVersionTotal();

	@SQL("select count(id) from app_dest_resource where dest_id=:1")
	public int findResourceTypeCount(int destId);

	@SuppressWarnings("rawtypes")
	@SQL("select t1.id,t1.name,t1.enname,t1.resource_type resourceType,"
			+ "t1.dest_id destId,t1.parent_id parentId,t1.node_count nodeCount,"
			+ "t1.leaf_count leafCount,t1.layer_index layerIndex,t1.tree_index treeIndex,"
			+ "t4.mini_img miniImg,t1.rel_id relId "
			+ " from app_dest_resource t1  join app_dest_img_resource t4 on t1.id = t4.attach_resource_id  and t4.leaf = 0  "
			+ " where t1.dest_id = :1 and t1.resource_type=2"
			+ " order by t1.id desc ")
	public List<Map> findListResourceType(int destId);

	@SQL("select * from app_dest_resource where dest_id=:1 and name=:2")
	public DestResourcePOJO findDestResourceTypeByNameAndDestId(int destId,
			String name);

	@SQL("select max(layer_index) from app_dest_resource where parent_id =:1")
	public Integer findMaxOrderByParentId(int destId);

	@SQL("update app_dest_resource set node_count = node_count+1 where id=:1")
	public void updateNodeCount(int destId);

	@SQL("update app_dest_resource set leaf_count = leaf_count+1 where id=:1")
	public void updateLeafCount(int id);

	@SuppressWarnings("rawtypes")
	@SQL("select t1.id,t1.name,t1.enname,t1.resource_type resourceType,t1.rel_id relId,"
			+ "t1.dest_id destId,t1.parent_id parentId,t1.node_count nodeCount,"
			+ "t1.leaf_count leafCount,t1.layer_index layerIndex,t1.tree_index treeIndex,"
			+ "t4.mini_img miniImg,t5.address,t5.telephone,t5.email,t5.site  "
			+ " from app_dest_resource t1  join app_dest_img_resource t4 " +
					"on t1.id = t4.attach_resource_id and t4.leaf = 0  " +
					"join app_dest_resource_ext t5 on t1.id = t5.resource_id "
			+ " where t1.parent_id = :1 " + " order by t1.id desc ")
	public List<Map> findListResourceTypeItem(int resourceTypeId);

	/**
	 * 保存目的地的文本信息（描述信息）
	 * 
	 * @param text
	 */
	@SQL("insert into app_dest_text_resource(resource_id,resource_desc) "
			+ "values(:1.resourceId,:1.resourceDesc)")
	public void saveDestText(DestTextResourcePOJO text);

	/**
	 * 保存资源项的扩展信息
	 * 
	 * @param itemPOJO
	 */
	@SQL("insert into app_dest_resource_ext(resource_id,address,telephone,email,site,price,trade_time) "
			+ "values(:1.resourceId,:1.address,:1.telephone,:1.email,:1.site,:1.price,:1.tradeTime)")
	public void saveResourceExt(DestResourceExtPOJO itemPOJO);

	@SQL("insert into  app_dest_img_resource(attach_resource_id,img_summary,mini_img,orig_img,"
			+ "dest_id,parent_id,resource_type,layer_index,leaf) "
			+ "values(:1.attachResourceId,:1.imgSummary,:1.miniImg,:1.origImg,:1.destId,"
			+ ":1.parentId,:1.resourceType,:1.layerIndex,:1.leaf)")
	public void saveImgResource(DestImgResourcePOJO imgPOJO);

	@SQL("update app_dest_img_resource set img_summary=:1.imgSummary,"
			+ "orig_img=:1.origImg,mini_img=:1.miniImg where attach_resource_id=:1.attachResourceId and leaf = 0")
	public void updateImg(DestImgResourcePOJO imgPOJO);

	@SQL("select max(layer_index) from app_dest_img_resource where attach_resource_id =:1")
	public Integer findMaxLayerIndexId(int attachResourceId);

	@SQL("select * from app_dest_img_resource where attach_resource_id =:1 and leaf != 0")
	public List<DestImgResourcePOJO> findListImgs(int destId);

	@SQL("select name from app_dest_resource where id=:1")
	public String findName(int id);

	@SQL("update app_dest_resource set dest_id = :1 where id =:1")
	public void updateDestDestId(int destId);

	@SQL("select * from app_dest_img_resource where dest_id = :1")
	public List<DestImgResourcePOJO> findDestAllImages(int destId);

	@SQL("update app_dest_img_resource set tree_index =:1.treeIndex,tree_leaf_index = :1.treeLeafIndex where id=:1.id")
	public void updateResourceImagesIndex(List<DestImgResourcePOJO> images);
	
	/**
	 * 更新资源的顺序
	 * @param resources
	 */
	@SQL("update app_dest_resource set tree_index =:1.treeIndex where id=:1.id")
	public void updateResourceTreeIndex(List<DestResourcePOJO> resources);

	@SQL("SELECT id,name FROM app_dest_resource where parent_id = 0")
	public List<DestResourcePOJO> findListDestName();

	@SQL("select t1.*,t2.mini_img miniImg,t2.orig_img origImg from  app_dest_resource t1 "
			+ "join app_dest_img_resource t2 on t1.id = t2.attach_resource_id "
			+ "where t1.dest_id = :1 and t2.leaf=0 order by tree_index ")
	public List<DestVO> findDestAllResource(int destId);

	@SQL("select * from app_dest_img_resource where dest_id= :1 order by tree_index")
	public List<DestImgResourcePOJO> findDestImg(int destId);

	@SQL("select * from app_dest_text_resource where resource_id=:1")
	public DestTextResourcePOJO findDestTextResourcePOJOById(int resourceId);

	@SQL("select * from app_dest_resource_ext where resource_id=:1")
	public DestResourceExtPOJO findDestResourceExtPOJOById(int resourceId);

	@SQL("delete from app_dest_img_resource where id=:1")
	public void deleteImg(int imgId);

	@SQL("update app_dest_resource set leaf_count=leaf_count-1 where id=:1")
	public void updateDestResourceLeafCount(int destId);
	@SQL("update app_dest_resource_ext set address=:1.address,telephone=:1.telephone,email=:1.email," +
			"site=:1.site,price=:1.price,trade_time=:1.tradeTime where resource_id=:1.resourceId")
	public void updateDestResourceExt(DestResourceExtPOJO extPOJO);
	@SQL("select * from app_dest_img_resource where id=:1")
	public DestImgResourcePOJO findDestResourceImg(int imgId);
	@SQL("update app_dest_img_resource set img_summary=:1.imgSummary," +
			"mini_img=:1.miniImg,orig_img=:1.origImg where id =:1.id")
	public void updateDestResourceImg(DestImgResourceVO imgVO);
	@SQL("select * from app_dest_img_resource where attach_resource_id=:1 and leaf=0")
	public DestImgResourcePOJO findImgPOJOByAttachResourceIdAndleaf(int attachResourceId);
	@SQL("insert into app_dest_ext(dest_id,country,altitude,people_num,dest_language," +
			"currency,netsite,telephone,aviation,railroad,highway,water_transport,local_transport) " +
			"values(:1.destId,:1.country,:1.altitude,:1.peopleNum,:1.destLanguage,:1.currency," +
			":1.netsite,:1.telephone,:1.aviation,:1.railroad,:1.highway,:1.waterTransport," +
			":1.localTransport)")
	public void saveDestExt(DestExtPOJO destExt);
	@SQL("update app_dest_ext set country=:1.country,altitude=:1.altitude,people_num=:1.peopleNum," +
			"dest_language=:1.destLanguage,currency=:1.currency,netsite=:1.netsite,telephone=:1.telephone," +
			"aviation=:1.aviation,railroad=:1.railroad,highway=:1.highway,water_transport=:1.waterTransport," +
			"local_transport=:1.localTransport where dest_id=:1.destId")
	public void updateDestExt(DestExtPOJO destExt);
	@SQL("SELECT * FROM app_dest_ext where dest_id=:1")
	public DestExtPOJO findDestExtById(int destId);
	@SQL("SELECT id,traffic_type,title,left(content,150) content,paragragh_order " +
			"FROM app_dest_traffic where dest_id=:1 order by traffic_type,paragragh_order")
	public List<DestTrafficPOJO> findListDestTraffic(int destId);
	@SQL("select max(paragragh_order) from app_dest_traffic where dest_id=:1 and traffic_type=:2")
	public Integer findMaxParagraghOrder(int destId,int trafficTypeId);
	@SQL("insert into app_dest_traffic(dest_id,traffic_type,title,content,paragragh_order) " +
			"values(:1.destId,:1.trafficType,:1.title,:1.content,:1.paragraghOrder)")
	public void saveDestTraffic(DestTrafficPOJO traffic);
	@SQL("update app_dest_traffic set title=:1.title," +
			"content=:1.content where  id=:1.id ")
	public void updateDestTraffic(DestTrafficPOJO traffic);
	@SQL("select * from app_dest_traffic where id=:1")
	public DestTrafficPOJO findDestTrafficById(int trafficId);
	@SQL("delete from app_dest_traffic where id=:1")
	public void deleteDestTraffic(int trafficId);
	@SQL("select * from app_dest_traffic where dest_id=:1 and traffic_type=:2 " +
			" order by paragragh_order" )
	public List<DestTrafficPOJO> findListDestTrafficByTypeId(int destId,int typeId);
}
